﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Drawing;
using System.IO;
namespace BookManage
{
    class ExcelHelper
    {
        public static void ExportToExcel(System.Data.DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null) return;

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;
            for(int i=0;i<dt.Columns.Count;i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    //worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();  //已导出。问题：日期含时间，图片不能导出

                    if (dt.Rows[r][i].GetType().FullName == "System.DateTime")  //处理日期
                        worksheet.Cells[r + 2, i + 1] = Convert.ToDateTime(dt.Rows[r][i].ToString()).ToString("yyyy/mm/dd");//yyyy_mm_dd 时间转换不过来
                    else if (dt.Rows[r][i].GetType().FullName == "System.Byte[]" && dt.Rows[r][i] != DBNull.Value)  //导出图片
                    {
                        MemoryStream ms = new MemoryStream((Byte[])dt.Rows[r][i]);
                        Image img = Bitmap.FromStream(ms, true);
                        range = (Range)worksheet.Cells[r + 2, i + 1];
                        PlacePicture(img, range);
                    }
                    else
                        worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            xlApp.Visible = true;
        }

        private static void PlacePicture(Image picture,Range destination)
        {
            Worksheet ws = destination.Worksheet;
            Clipboard.SetImage(picture);  //图片复制到系统剪贴板
            ws.Paste(destination, false);  //从剪贴板粘贴到Cell位置
            //将图片缩放到Cell内，注释下面3行试一试
            Pictures p = ws.Pictures(System.Reflection.Missing.Value) as Pictures;
            Picture pic = p.Item(p.Count) as Picture;
            ScalePicture(pic, (double)destination.Width, (double)destination.Height);
        }

        private static void ScalePicture(Picture pic, double width, double height)  //缩放图片
        {
            double fX = width / pic.Width;
            double fY = height / pic.Height;
            double oldH= pic.Height;
            if(fX<fY)
            {
                pic.Width *= fX;
                if (pic.Height == oldH)  //no change if sapect ratic is locked
                    pic.Height *= fX;
                pic.Top += (height - pic.Height) / 2;
            }
            else
            {
                pic.Width *= fY;
                if (pic.Height == oldH)  //
                    pic.Height *= fY;
                pic.Left +=(width - pic.Width) / 2;
            }
        }
    }
}
